clear all
cap close
set more off

cd ".\Data_Program"

*===============================================================================
* CALL REPORT: beginning of 2008 
*===============================================================================
* 1. Bank info
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312007\FFIEC CDR Call Bulk POR 12312007.txt",clear
keep idrssd financialinstitutionname financialinstitutionfilingtype
rename financialinstitutionname callname
rename financialinstitutionfilingtype type
save bankinfo,replace


* 2. T1ratio: tier 1 capital/total risk-weighted assets
   * part 0 msr
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312007\FFIEC CDR Call Schedule RCM 12312007.txt", clear  
keep idrssd rcfd3164 rcon3164
drop if _n==1
save part0,replace

   * part 1
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312007\FFIEC CDR Call Schedule RCR 12312007(1 of 2).txt", clear

keep idrssd rcfda223 rcfd8274  rcon8274 rcfd2170  rcon2170

drop if _n==1
save part1,replace

   * part 2
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312007\FFIEC CDR Call Schedule RCR 12312007(2 of 2).txt", clear
keep idrssd rcona223 
drop if _n==1

merge 1:1 idrssd using part1
drop _merge
merge 1:1 idrssd using part0
drop _merge

destring *,replace

merge 1:1 idrssd using bankinfo
drop _merge // all matched

    gen t1cap = rcfd8274 if type==31
replace t1cap = rcon8274 if type==41

    gen rwa = rcfda223 if type==31
replace rwa = rcona223 if type==41

    gen tota = rcfd2170 if type==31
replace tota = rcon2170 if type==41

gen t1ratio=t1cap/rwa

    * msr: mortgage servicing asset/T1 capital
    gen msra = rcfd3164 if type==31
replace msra = rcon3164 if type==41	

gen msr = msra/t1cap

keep id callname type t1ratio msr msra t1cap tota
gen year=2008
save call2008,replace

erase part1.dta
erase part0.dta
erase bankinfo.dta


*===============================================================================
* CALL REPORT: end of 2015
*===============================================================================
* 1. Bank info
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312015\FFIEC CDR Call Bulk POR 12312015.txt",clear
keep idrssd financialinstitutionname financialinstitutionfilingtype
rename financialinstitutionname callname
rename financialinstitutionfilingtype type   
save bankinfo,replace

* 2. T1ratio: tier 1 capital/total risk-weighted assets
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312015\FFIEC CDR Call Schedule RC 12312015.txt", clear
keep idrssd rcfd2170 rcon2170
drop if _n==1
destring *,replace
save tota2015,replace


* 3. T1ratio: tier 1 capital/total risk-weighted assets
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312015\FFIEC CDR Call Schedule RCRI 12312015.txt", clear
keep idrssd rcfa8274 rcfaa223 rcoa8274 rcoaa223 
drop if _n==1
destring *,replace

merge 1:1 idrssd using bankinfo
drop _merge // all matched

merge 1:1 idrssd using tota2015
drop _merge // all matched

    gen t1cap = rcfa8274 if type==31
replace t1cap = rcoa8274 if type==41

    gen rwa = rcfaa223 if type==31
replace rwa = rcoaa223 if type==41

    gen tota = rcfd2170 if type==31
replace tota = rcon2170 if type==41

gen t1ratio=t1cap/rwa

keep idrssd callname type t1ratio t1cap tota
save t12015,replace


* 4. msrratio = mortgag servicing assets/tier 1 capital
import delimited "Econdata\FFIEC CDR Call Bulk All Schedules 12312015\FFIEC CDR Call Schedule RCM 12312015.txt", clear 

keep idrssd rcfd3164 rcon3164
drop if _n==1
destring *,replace

    gen msra=rcfd3164  // mortgage servicing assets
replace msra=rcon3164 if rcon3164!=.

keep idrssd msra

merge 1:1 idrssd using t12015
drop _merge

gen msr= msra/t1cap

gen year=2015
save call2015,replace
erase bankinfo.dta
erase t12015.dta


*===============================================================================
* COMBINE CALL REPORTS 2008+2015 TO GET SINGLE CROSS-SECTION 
*===============================================================================
use call2008,clear  // baseline 2008
append using call2015

* change in capital ratio and msr ratio: 2008-2015
xtset id year   
by id: gen rep=_N
drop if rep==1 & year==2015  // keep 2008 banks (survive or exit in year d, 9%) 

gen survivor=(rep==2)

sort id year
by id: gen dcr  = t1ratio[2]-t1ratio[1]
by id: gen cr08 = t1ratio[1]
by id: gen cr15 = t1ratio[2]

by id: gen dmsr = msr[2]-msr[1]
by id: gen msr08 = msr[1]
by id: gen msr15 = msr[2]

by id: gen tota08 = tota[1]
by id: gen tota15 = tota[2]
by id: keep if _n==1

keep idrssd callname dcr cr08 cr15 survivor dmsr msr08 msr15 type tota*

drop type  // redefine banks according to total assets (reported in thous) 
* big4, Large:50B-1T, Intermediate: 10B-50B, Small: <10B
gen banktype = 1 if inlist(idrssd,480228,476810,852218,451965) //BoA, Citibank, JP Morgan, WF
replace banktype = 2 if tota08>5*10^7 & tota08<=10^9
replace banktype = 3 if tota08>10^7 & tota08<=5*10^7
replace banktype = 4 if tota08<=10^7

save call,replace


*===============================================================================
* PLOT T1 DISTRIBUTION
*===============================================================================

* Figure 4(a) T1 ratio in 2008 and 2015
use call,clear
scalar temp = 0.5   

sum cr08,d
replace cr08=. if cr08>temp

sum cr15,d
replace cr15=. if cr15>temp

twoway(hist cr08,fcolor(bluishgray)  lcolor(white) bin(70) start(0))(hist cr15, fcolor(none)  lcolor(orange) bin(70) start(0)), graphregion(color(white)) ylabel(0(5)15,nogrid) legend(pos(3) ring(0) col(1) lab(1 "2008 Distribution") lab(2 "2015 Distribution") region(color(none)) size(medium)) title("") ytitle("Density",size(large)) xtitle("T1 capital ratio",size(large))


* Figure 4(b) T1 ratio in 2008 and 2008-2015
use call,clear

scalar temp = 0.5  
drop if cr08>temp

xtile cr08_cat = cr08,nq(20)
tab cr08_cat,sum(cr08)

sort cr08_cat
by cr08_cat: egen cr08_cat_m = mean(cr08)
by cr08_cat: egen dcr_cat_m = mean(dcr)
by cr08_cat: keep if _n==1

twoway(scatter dcr_cat_m cr08_cat_m)(lfit dcr_cat_m cr08_cat_m,lwidth(0.2) lpattern(dash)),xlabel(0.05(0.05)0.4) ylabel(-0.075(0.025)0.05) legend(off) graphregion(color(white)) title("") xtitle("T1 capital ratio, 2008",size(large)) ytitle("T1 capital ratio, 2008-2015",size(large))  scheme(s2color)

